package com.zhiyou.util;

import com.zhiyou.entity.Column;
import com.zhiyou.entity.PowerCenter;
import com.zhiyou.entity.User;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author sanshi
 * @desc
 * @create 2021-09-15 10:46
 */
public class PWUtil extends DBUtil {
    public ArrayList<PowerCenter> getAllPC(){
        ArrayList<PowerCenter> list = new ArrayList<PowerCenter>();
        this.getConn();
        String sql = "select * from sys_user_chnl";
        try {
            ps = connection.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()){
                PowerCenter pc = new PowerCenter(rs.getString("acct"), rs.getString("chnl_code"), rs.getString("sys_code"));
                list.add(pc);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        this.closeConn();
        return list;
    }
    //对数据二次加工(加入user)
    public List<PowerCenter> getAllPC2(){
        ArrayList<PowerCenter> list = getAllPC();
        for (int i = 0; i < list.size(); i++) {
            String acct = list.get(i).getAcct();
            User user = getUserByAcct(acct);
            list.get(i).setUser(user);
        }
        return list;
    }
    //对数据三次加工(加入column)
    public List<PowerCenter> getAllPC3(){
        List<PowerCenter> list = getAllPC2();
        for (int i = 0; i < list.size() ; i++) {
            String chnlCode = list.get(i).getChnlCode();
            Column column = getColumnByChnlCode(chnlCode);
            list.get(i).setColumn(column);
        }
        return list;
    }

    //根据acct得到user对象
    public User getUserByAcct(String acct){
        User user = null;
        getConn();
        String sql = "select * from sys_user where acct = ?";
        try {
            ps = connection.prepareStatement(sql);
            ps.setString(1,acct);
            rs = ps.executeQuery();
            if (rs.next()){
                user = new User(rs.getString("acct"), rs.getString("pwd"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        closeConn();
        return user;
    }
    //根据chnlCode拿到column对象
    public Column getColumnByChnlCode(String chnlCode){
        Column column = null;
        getConn();
        String sql = "select * from news_chnl where id  = ?";
        try {
            ps = connection.prepareStatement(sql);
            ps.setString(1,chnlCode);
            rs = ps.executeQuery();
            if (rs.next()){
                column = new Column(rs.getString("id"),rs.getString("name"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        closeConn();
        return column;
    }
    //条件查询
    public List<PowerCenter> searchByCondition(String sql, Object[] params){
        ArrayList<PowerCenter> list = new ArrayList<PowerCenter>();
        this.getConn();
        try {
            ps = connection.prepareStatement(sql);
            for (int i = 0; i < params.length ; i++) {
                ps.setObject(i+1,params[i]);
            }
            rs = ps.executeQuery();
            while (rs.next()){
//                User user = getUserByAcct(rs.getString("acct"));
                PowerCenter pc = new PowerCenter(rs.getString("acct"), rs.getString("chnl_code"), rs.getString("sys_code"));
////                pc.setUser(user);
//                String acct = rs.getString("acct");
//                User user = getUserByAcct(acct);
//                pc.setUser(user);
                list.add(pc);
            }
            System.out.println(list.size());
        } catch (SQLException e) {
            e.printStackTrace();
        }
        this.closeConn();
        return list;
    }
    //查询条件第一次加工
    public List<PowerCenter> searchByCondition2(String sql,Object[] params){
        List<PowerCenter> list = searchByCondition(sql, params);
        for (int i = 0; i < list.size() ; i++) {
            String acct = list.get(i).getAcct();
            User user = getUserByAcct(acct);
            list.get(i).setUser(user);
        }
        return list;
    }
    //查询条件二次加工
    public List<PowerCenter> searchByCondition3(String sql ,Object[] params){
        List<PowerCenter> list = searchByCondition2(sql, params);
        for (int i = 0; i < list.size(); i++) {
            String chnlCode = list.get(i).getChnlCode();
            Column column = getColumnByChnlCode(chnlCode);
            list.get(i).setColumn(column);
        }
        return list;
    }
}
